package com.yjd.bi.etl.sql;

import com.yjd.bi.comm.utils.DbNameUtils;

public class MarketSqlConstants {
	// 查询最大统计日期
	public final static String GET_MAX_STATISTIC_DATE = "select MAX(statistical_date) from " + DbNameUtils.get("yjd_market_statistics");
	
	// 查询渠道id
	public final static String GET_CHANNEL_ID = "select id from " + DbNameUtils.get("yjd_market_channel");
	
	// 查询渠道推广用户数
	public final static String GET_REGISTER_USER_COUNT = "SELECT count(DISTINCT(user_id)) FROM " + DbNameUtils.get("yjd_market_user_statistics") + " t where t.channel_id = ? and t.reg_date BETWEEN ? and ?";
	
	// 查询充值用户数
	public final static String GET_RECHARGE_USER_COUNT = "SELECT count(DISTINCT(f.user_id)) FROM yjd_funds_data f, yjd_user u, ( SELECT DISTINCT (user_id) FROM yjd_market_user_statistics WHERE channel_id = ? ) a WHERE f.user_id = u.user_id AND u.user_id = a.user_id AND f.type IN (1, 2) AND u.user_type & 4 != 4 AND uptime BETWEEN ? and ?";
	
	// 查询投资用户数
	public final static String GET_INVEST_USER_COUNT = "SELECT count(DISTINCT(a.user_id)) FROM ( SELECT ct.buy_user_id as user_id FROM " + DbNameUtils.get("yjd_creditor_transfer") + " ct WHERE oktime BETWEEN ? AND ? AND  `status` = 3 UNION ALL SELECT i.user_id FROM " + DbNameUtils.get("yjd_invest") + " i 	WHERE 	addtime BETWEEN ? AND ? UNION ALL SELECT w.user_id FROM " + DbNameUtils.get("yjd_wal_project_invest") + " w WHERE create_time BETWEEN ? AND ? ) a ,(SELECT DISTINCT (user_id) FROM yjd_market_user_statistics WHERE channel_id = ?)b WHERE a.user_id = b.user_id";
	
	// 查询充值金额
	public final static String GET_RECHARGE_MONEY_COUNT = "SELECT IFNULL(sum(price), 0.00) FROM " + DbNameUtils.get("yjd_funds_data") +" f, "+DbNameUtils.get("yjd_user") + " u, ( SELECT DISTINCT (user_id) FROM " + DbNameUtils.get("yjd_market_user_statistics") + " WHERE channel_id = ? )s WHERE f.user_id = u.user_id AND f.type IN (1, 2) AND u.user_type & 4 != 4 AND f.user_id = s.user_id AND uptime BETWEEN ? and ?";
		
	// 查询投资金额
	public final static String GET_INVEST_MONEY_COUNT = "SELECT IFNULL(sum(price),0.00) FROM ( SELECT ct.price,ct.buy_user_id as user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ? AND  `status` = 3 UNION ALL SELECT i.price,i.user_id FROM "+DbNameUtils.get("yjd_invest")+" i 	WHERE 	addtime BETWEEN ? AND ? UNION ALL SELECT w.price,w.user_id FROM "+DbNameUtils.get("yjd_wal_project_invest")+" w WHERE create_time BETWEEN ? AND ? ) a,(SELECT DISTINCT(user_id) FROM "+DbNameUtils.get("yjd_market_user_statistics") +" where channel_id = ?)s  WHERE a.user_id = s.user_id";

	public static final String MARKET_STATISTIC_INSERTSQL = "insert into "+DbNameUtils.get("yjd_market_statistics")+"(statistical_date,channel_id,register_account,recharge_account,invest_account,recharge_money,invest_money,create_time) VALUES (?,?,?,?,?,?,?,unix_timestamp())";

}
